package com.me.dao.impl;

import com.me.dao.IVetDao;
import com.me.domain.Speciality;
import com.me.domain.Vet;
import com.me.util.DBHelper;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class VetDao implements IVetDao {

    @Override
    public List<Vet> search(String vetName, String specName) {
        try {
            QueryRunner runner = new QueryRunner(DBHelper.getDatasource());
            String sql = "SELECT v.* FROM vet v,vet_speciality vs,speciality s " +
                    "where v.id=vs.vetID AND s.id=vs.specID AND v.`name` like ? AND s.name like ?;";
            List<Vet> vets = runner.query(sql, new BeanListHandler<Vet>(Vet.class),"%"+vetName+"%","%"+specName+"%");
            for (Vet v:vets){
                String sql2 = "SELECT speciality.* from vet_speciality " +
                        "INNER JOIN speciality ON (vet_speciality.specId=speciality.id) " +
                        "INNER JOIN pet.vet ON(vet_speciality.vetId=vet.id) " +
                        "where vet.id="+v.getId();

                v.setSpecs(runner.query(sql2,new BeanListHandler<Speciality>(Speciality.class)));
            }
            return vets;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    public Vet getById(int id) {
        try {
            QueryRunner runner = new QueryRunner(DBHelper.getDatasource());
            String sql = "select * from vet v where v.id=?";
            return runner.query(sql,new BeanHandler<Vet>(Vet.class),id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    public void save(Vet vet){
        try {
            QueryRunner runner = new QueryRunner(DBHelper.getDatasource());
            String sql = "insert into vet value(null,?)";
            runner.update(sql,vet.getName());

            String sql0 = "select * from vet order by id desc";
            Vet v = runner.query(sql0, new BeanHandler<Vet>(Vet.class));

            String sql1 = "delete from vet_speciality where vetID=?";
            runner.update(sql1, v.getId());

            List<Speciality> specs = vet.getSpecs();
            for (int i = 0; i < specs.size(); i++) {
                String sql2 = "INSERT INTO `pet`.`vet_speciality` (`vetID`, `specID`) VALUES (?, ?)";
                runner.execute(sql2,v.getId(),specs.get(i).getId());
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
    }
    public List<Vet> getAll(){
        try {
            QueryRunner runner = new QueryRunner(DBHelper.getDatasource());
            String sql = "select * from vet";
            List<Vet> vets = runner.query(sql, new BeanListHandler<Vet>(Vet.class));
            for (Vet v:vets){
                String sql2 = "SELECT speciality.* from vet_speciality " +
                        "INNER JOIN speciality ON (vet_speciality.specId=speciality.id) " +
                        "INNER JOIN pet.vet ON(vet_speciality.vetId=vet.id) " +
                        "where vet.id="+v.getId();

                v.setSpecs(runner.query(sql2,new BeanListHandler<Speciality>(Speciality.class)));
            }
            return vets;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
    public void update(Vet vet) {
        try {
            QueryRunner runner = new QueryRunner(DBHelper.getDatasource());
            String sql = "UPDATE `pet`.`vet` SET `name` = ? WHERE (`id` = ?);";
            runner.update(sql,vet.getName(),vet.getId());

            String sql1 = "delete from vet_speciality where vetID=?";
            runner.update(sql1, vet.getId());

            List<Speciality> specs = vet.getSpecs();
            for (int i = 0; i < specs.size(); i++) {
                String sql2 = "INSERT INTO `pet`.`vet_speciality` (`vetID`, `specID`) VALUES (?, ?)";
                runner.update(sql2,vet.getId(),specs.get(i).getId());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
    public void delete(Vet user) {
        try {
            QueryRunner runner = new QueryRunner((DBHelper.getDatasource()));
            String sql = "DELETE FROM `pet`.`vet` WHERE (`id` = ?);";
            runner.update(sql,user.getId());
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public Vet getByName(String name) {
        try {
            QueryRunner runner = new QueryRunner((DBHelper.getDatasource()));
            String sql = "select * from vet where name=?";
            return runner.query(sql,new BeanHandler<Vet>(Vet.class),name);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return null;
    }
}
